package com.zl.tby.core.base.util;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 巡山护林数据库
 * @author changpeng
 * @version 1.0
 * @since 2024/10/22 22:27
 */
public class PgMountains<T> {

    private static String jdbcUrl;
    private static String username;
    private static String password;

    public static void init(String jdbcUrl, String username, String password) {
        PgMountains.jdbcUrl = jdbcUrl;
        PgMountains.username = username;
        PgMountains.password = password;
    }

    public static Connection getConnection() {
        //String jdbcUrl = "jdbc:postgresql://175.44.137.61:15432/fjlzzdb";
        String jdbcUrl = "jdbc:postgresql://172.20.1.7:15432/fjlzzdb";
        String username = "patrolother";
        String password = "patrolother";
        try {
            Class.forName("org.postgresql.Driver");
            return DriverManager.getConnection(jdbcUrl, username, password);
            //connection.close();
        } catch (ClassNotFoundException e) {
            System.out.println("PostgreSQL JDBC 驱动未找到");
            e.printStackTrace();
        } catch (SQLException e) {
            System.out.println("数据库连接失败");
            e.printStackTrace();
        }
        return null;
    }

    public List<Map<String, Object>> queryMap(String sql) {
        return queryMap(sql, null);
    }

    public List<Map<String, Object>> queryMap(String sql, List<Object> params) {
        return queryMap(getConnection(), sql, params, true);
    }

    public List<Map<String, Object>> queryMap(Connection conn, String sql, List<Object> param, boolean ifClose) {
        if (conn == null) {
            conn = getConnection();
        }
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            statement = conn.prepareStatement(sql);
            if (param != null && param.size() > 0) {
                int size = param.size();
                for (int i = 0; i < size; i++) {
                    if (param.get(i) instanceof java.util.Date) {
                        statement.setObject(i + 1, new Timestamp(((java.util.Date) param.get(i)).getTime()));
                    } else {
                        statement.setObject(i + 1, param.get(i));
                    }
                }
            }
            resultSet = statement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnNum = metaData.getColumnCount();
            List<Map<String, Object>> list = new ArrayList<>();
            while (resultSet.next()) {
                Map<String, Object> rowData = new HashMap<>();
                for (int i = 1; i <= columnNum; i++) {
                    rowData.put(metaData.getColumnName(i), resultSet.getObject(i));
                }
                list.add(rowData);
            }
            return list;
        } catch (Exception ex) {
            ex.printStackTrace();
            return new ArrayList<>();
        } finally {
            closeRs(resultSet);
            closeStat(statement);
            if (ifClose) {
                closeConn(conn);
            }
        }
    }

    public List<T> query(String sql, Class<T> clazz) {
        return query(sql, null, clazz);
    }

    public List<T> query(String sql, List<Object> param, Class<T> clazz) {
        return query(getConnection(), sql, param, true, clazz);
    }

    public List<T> query(Connection conn, String sql, List<Object> param, boolean ifClose, Class<T> clazz) {
        if (conn == null) {
            conn = getConnection();
        }
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            statement = conn.prepareStatement(sql);
            if (param != null && param.size() > 0) {
                int size = param.size();
                for (int i = 0; i < size; i++) {
                    if (param.get(i) instanceof java.util.Date) {
                        statement.setObject(i + 1, new Timestamp(((java.util.Date) param.get(i)).getTime()));
                    } else {
                        statement.setObject(i + 1, param.get(i));
                    }
                }
            }
            resultSet = statement.executeQuery();
            return convertListEntity(clazz, resultSet);
        } catch (Exception ex) {
            ex.printStackTrace();
            return new ArrayList<>();
        } finally {
            closeRs(resultSet);
            closeStat(statement);
            if (ifClose) {
                closeConn(conn);
            }
        }
    }

    private static void closeRs(ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    private static void closeStat(PreparedStatement statement) {
        try {
            if (statement != null) {
                statement.close();
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    public static void closeConn(Connection conn) {
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    public List<T> convertListEntity(Class<T> clazz, ResultSet rs)  {
        Field field = null;
        List<T> lists = new ArrayList<T>();
        try {
            ResultSetMetaData metaData = rs.getMetaData();
            int counts = metaData.getColumnCount();
            String[] columnNames = new String[counts];
            for (int i = 0; i < counts; i++) {
                columnNames[i] = metaData.getColumnLabel(i + 1);
            }
            while (rs.next()) {
                T t = clazz.newInstance();
                for (int i = 0; i < counts; i++) {
                    Object value = null;
                    Class<?> dbType = null;
                    try {
                        value = rs.getObject(columnNames[i]);
                        dbType = value.getClass();
                    } catch (Exception e) {
                        continue;
                    }
                    field = clazz.getDeclaredField(columnNames[i]);
                    Class<?> beanType = field.getType();
                    String setMethodName = "set" + firstUpperCase(columnNames[i]);
                    Method m = t.getClass().getMethod(setMethodName,beanType);
                    if (value instanceof java.sql.Timestamp) {
                        m.invoke(t, new java.util.Date(((java.sql.Timestamp) value).getTime()));
                        continue;
                    } else if (value instanceof Double) {
                        m.invoke(t, new BigDecimal(value + ""));
                        continue;
                    }
                    // 就自然灾害一个表的respond_status是long，其他都是int，这里单独处理下
                    if (columnNames[i].equals("respondStatus")) {
                        try {
                            Long tempValue = (Long) value;
                            if (tempValue != null) {
                                m.invoke(t, tempValue.intValue());
                            }
                            continue;
                        } catch (Exception ex) {
                            Integer tempValue = (Integer) value;
                            if (tempValue != null) {
                                m.invoke(t, tempValue.intValue());
                            }
                            continue;
                        }
                    }
                    if (beanType != dbType) {
                        value = rs.getString(columnNames[i]);
                    }
                    m.invoke(t, value);
                }
                lists.add(t);
            }
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
        return lists;
    }

    public static String firstUpperCase(String old){
        return old.substring(0, 1).toUpperCase()+old.substring(1);
    }
}
